CREATE procedure [dbo].[BAEEventSetupClone] @newEventCode as varchar(15),@oldEventCode as varchar(15) AS

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      Select * from EventSetupGeneral where UPPER(EventCode) = Upper(@newEventCode);

      If(@@rowcount = 0)
            BEGIN             
                  BEGIN TRANSACTION
                        Insert into EventSetupGeneral Select @newEventCode, OnlineCutOffDate, OnlineCutOffMessage, RegistrantClassType, RegistrantClassStoredProcedure, RegisterSomeoneElseOption, PermanentChange from EventSetupGeneral where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupLabel Select @newEventCode, StartRegistrationLabel, RegistrantLabel, FunctionsLabel, GuestLabel, QuestionLabel, SummaryLabel, QuestionPageMessage from EventSetupLabel where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupGuest Select @newEventCode, GuestPageMessage, FirstName, FirstNameEnabled, FirstNameRequired, MiddleInitial, MiddleInitialEnabled, MiddleInitialRequired, LastName, LastNameEnabled, LastNameRequired, Address, AddressEnabled, AddressRequired, City, CityEnabled, CityRequired, StateProvince, StateEnabled, StateRequired, PostalCode, PostalCodeEnabled, PostalCodeRequired, Country, CountryEnabled, CountryRequired, Prefix, PrefixEnabled, PrefixRequired, Suffix, SuffixEnabled, SuffixRequired, Nickname, NicknameEnabled, NicknameRequired, Designation, DesignationEnabled, DesignationRequired, Title, TitleEnabled, TitleRequired, Company, CompanyEnabled, CompanyRequired  from EventSetupGuest where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupRegistrant Select @newEventCode, RegistrantPageMessage, FirstName, FirstNameEnabled, FirstNameRequired, MiddleInitial, MiddleInitialEnabled, MiddleInitialRequired, LastName, LastNameEnabled, LastNameRequired, Address, AddressEnabled, AddressRequired, City, CityEnabled, CityRequired, StateProvince, StateEnabled, StateRequired, PostalCode, PostalCodeEnabled, PostalCodeRequired, Phone, PhoneEnabled, PhoneRequired, Email, EmailEnabled, EmailRequired, Country, CountryEnabled, CountryRequired from EventSetupRegistrant where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupGroup Select @newEventCode, GroupName, GroupLabel, GroupOrder, GroupPageMessage from EventSetupGroup where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupFunctionGroup Select @newEventCode, FunctionCode, GroupName, FunctionOrder from EventSetupFunctionGroup where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupQuestion Select @newEventCode, FunctionCode, Question, QuestionType, Required, QuestionOrder from EventSetupQuestion where Upper(EventCode) = Upper(@oldEventCode);

                        Insert into EventSetupQuestionChoice
                            Select EventSetupQuestionChoiceID, new.EventSetupQuestionID, Answer
                            from EventSetupQuestionChoice a, EventSetupQuestion new, EventSetupQuestion old
                            where a.EventSetupQuestionID = old.EventSetupQuestionID and Upper(new.EventCode) = Upper(@newEventCode)
                            and Upper(old.EventCode) = Upper(@oldEventCode) and new.FunctionCode = old.FunctionCode
                            and new.QuestionOrder = old.QuestionOrder;
                  COMMIT
            END
END

GO
GRANT EXECUTE ON  [dbo].[BAEEventSetupClone] TO [IMIS]
GO